﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Cocbook_DTO;

namespace Cocbook_DAL
{
     public class CustomerDAL
    {
         public bool CreateCustomer(Customer c)
         {
             DB.SetQuery("INSERT INTO Customer VALUES (@cmnd,@name,@birthday,@address,@phone,@tel,@point,@email)");
             DB.AddParameters("name",c.CName);
             DB.AddParameters("cmnd", c.CMND);
             if (c.Birthday == DateTime.MinValue)
             {
                 c.Birthday = DateTime.Parse("1/1/1800");
             }
             DB.AddParameters("birthday", c.Birthday);
             DB.AddParameters("address", c.CAddress);
             DB.AddParameters("phone", c.Phone);
             DB.AddParameters("tel", c.Tel);
             DB.AddParameters("point", c.Points);
             DB.AddParameters("email", c.Email);
             try
             {
                 DB.ExeNoneQuery();
                 return true;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.CreateCustomer", e.ToString());
                 return false;
             }
         }

         public bool UpdateCustomer(Customer c)
         {
             DB.SetQuery("UPDATE Customer SET CName=@name, CMND = @cmnd, Birthday=@birthday, CAddress=@address, Phone=@phone, Tel=@tel, Points=@point, Email = @email WHERE CustomerID=@id");
             DB.AddParameters("id", c.CustomerID);
             DB.AddParameters("cmnd", c.CMND);
             DB.AddParameters("name", c.CName);
             DB.AddParameters("birthday", c.Birthday);
             DB.AddParameters("address", c.CAddress);
             DB.AddParameters("phone", c.Phone);
             DB.AddParameters("tel", c.Tel);
             DB.AddParameters("point", c.Points);
             DB.AddParameters("email", c.Email);
            try
             {
                 DB.ExeNoneQuery();
                 return true;
             }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.UpdateCustomer", e.ToString());
                 return false;
             }
         }
         public bool DeleteCustomer(int c)
         {
             DB.SetQuery("DELETE Customer WHERE CustomerID=@id");
             DB.AddParameters("id",c);
             try
             {
                 DB.ExeNoneQuery();
                 return true;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.DeleteCustomer", e.ToString());
                 return false;
             }
         
         }



         public Customer GetCustomerById(int id)
         {
             try 
             {
                 DB.SetQuery("SELECT  CustomerID, CMND, CName, Birthday,CAddress,Phone,Tel,points,email FROM Customer WHERE CustomerID=@id ");
                 DB.AddParameters("id", id);
                 List<List<object>> lol = new List<List<object>>();
                 lol = DB.ExeQuery();
                 Customer c = new Customer();
                 if (lol.Count > 0) 
                 {
                     c.CustomerID = Convert.ToInt16(lol[0][0]);
                     c.CMND = Convert.ToInt32(lol[0][1]);
                     c.CName = Convert.ToString(lol[0][2]);
                     c.Birthday = Convert.ToDateTime(lol[0][3]);
                     c.CAddress=Convert.ToString(lol[0][4]);
                     c.Phone=Convert.ToString(lol[0][5]);
                     c.Tel = Convert.ToString(lol[0][6]);
                     c.Points = Convert.ToInt32(lol[0][7]);
                     c.Email = Convert.ToString(lol[0][8]);
                 }
                 else
                 {
                     "".Substring(2);
                 }
                 return c;  
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetCustomerByID", e.ToString());
                 return null;
             }
         
         }


         public Customer GetCustomerByCMND(Int32 id)
         {
             try 
             {
                 DB.SetQuery("SELECT  CustomerID, CMND, CName, Birthday,CAddress,Phone,Tel,points,email FROM Customer WHERE CMND=@id ");
                 DB.AddParameters("id", id);
                 List<List<object>> lol = new List<List<object>>();
                 lol = DB.ExeQuery();
                 Customer c = new Customer();
                 if (lol.Count > 0)
                 {
                     c.CustomerID = Convert.ToInt16(lol[0][0]);
                     c.CMND = Convert.ToInt32(lol[0][1]);
                     c.CName = Convert.ToString(lol[0][2]);
                     c.Birthday = Convert.ToDateTime(lol[0][3]);
                     c.CAddress = Convert.ToString(lol[0][4]);
                     c.Phone = Convert.ToString(lol[0][5]);
                     c.Tel = Convert.ToString(lol[0][6]);
                     c.Points = Convert.ToInt32(lol[0][7]);
                     c.Email = Convert.ToString(lol[0][8]);
                 }
                 else
                 {
                     "".Substring(2);
                 }
                 return c;  
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetCustomerByID", e.ToString());
                 return null;
             }
         
         }


         public List<Customer> GetAllCustomer()
         {
             try
             {
                 DB.SetQuery(" SELECT  CustomerID, CMND, CName, Birthday,CAddress,Phone,Tel,points, Email FROM Customer ");
                 List<List<object>> lol = new List<List<object>>();
                 lol = DB.ExeQuery();
                 List<Customer> lc = new List<Customer>();
                 if (lol.Count > 0)
                 {
                     foreach (List<object> lo in lol)
                     {
                         Customer c = new Customer();
                         c.CustomerID = Convert.ToInt16(lo[0]);
                         c.CMND = Convert.ToInt32(lo[1]);
                         c.CName = Convert.ToString(lo[2]);
                         c.Birthday = Convert.ToDateTime(lo[3]);
                         c.CAddress = Convert.ToString(lo[4]);
                         c.Phone = Convert.ToString(lo[5]);
                         c.Tel = Convert.ToString(lo[6]);
                         c.Points = Convert.ToInt32(lo[7]);
                         c.Email = Convert.ToString(lo[8]);
                         lc.Add(c);
                     }
                 }
                 else
                 {
                     "".Substring(2);
                 }
                 return lc;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllCustomer", e.ToString());
                 return null;
             } 
         
         }

         public List<CustomerView> GetAllCustomerByFilterToCV(Int32 cmnd, string name,int month, int year, int day)
         {
             try
             {
                 DB.SetQuery("SELECT  CustomerID, CMND, CName, Birthday,CAddress,Phone,Tel,Points, Email FROM Customer " +
                             "WHERE   ((CAST(CMND AS nvarchar(10)) LIKE @cmnd OR @cmnd = '0%')) AND CMND <> 0 AND" +
                                     "((CName LIKE @name) OR (@name = '%%')) AND " +
                                     "((Month(Birthday) = @month) OR (@month = 0)) AND "+
                                     "((Year(Birthday) = @year) OR (@year = 0)) AND "+
                                     "((Day(Birthday) = @day) OR (@day = 0)) ");
                 DB.AddParameters("cmnd", cmnd.ToString()+"%");
                 DB.AddParameters("name", "%" + name + "%");
                 DB.AddParameters("month", month);
                 DB.AddParameters("year", year);
                 DB.AddParameters("day", day);
                 List<List<object>> lol = new List<List<object>>();
                 lol = DB.ExeQuery();
                 List<CustomerView> result = new List<CustomerView>();
                 if (lol.Count > 0)
                 {
                     foreach (List<object> lo in lol)
                     {
                         Customer c = new Customer();
                         c.CustomerID = Convert.ToInt16(lo[0]);
                         c.CMND = Convert.ToInt32(lo[1]);
                         c.CName = Convert.ToString(lo[2]);
                         c.Birthday = Convert.ToDateTime(lo[3]);
                         c.CAddress = Convert.ToString(lo[4]);
                         c.Phone = Convert.ToString(lo[5]);
                         c.Tel = Convert.ToString(lo[6]);
                         c.Points = Convert.ToInt32(lo[7]);
                         c.Email = Convert.ToString(lo[8]);
                         CustomerView cv = new CustomerView(c);
                         result.Add(cv);
                     }
                 }
                 else
                 {
                     "".Substring(2);
                 }
                 return result;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllCustomer", e.ToString());
                 return null;
             }

         }

         public List<CustomerView> GetAllCustomerToCV()
         {
             try
             {
                 DB.SetQuery(" SELECT  CustomerID, CMND, CName, Birthday,CAddress,Phone,Tel,Points, Email FROM Customer WHERE CMND <> 0 ");
                 List<List<object>> lol = new List<List<object>>();
                 lol = DB.ExeQuery();
                 List<CustomerView> result = new List<CustomerView>();
                 if (lol.Count > 0)
                 {
                     foreach (List<object> lo in lol)
                     {
                         Customer c = new Customer();
                         c.CustomerID = Convert.ToInt16(lo[0]);
                         c.CMND = Convert.ToInt32(lo[1]);
                         c.CName = Convert.ToString(lo[2]);
                         c.Birthday = Convert.ToDateTime(lo[3]);
                         c.CAddress = Convert.ToString(lo[4]);
                         c.Phone = Convert.ToString(lo[5]);
                         c.Tel = Convert.ToString(lo[6]);
                         c.Points = Convert.ToInt32(lo[7]);
                         c.Email = Convert.ToString(lo[8]);
                         CustomerView cv = new CustomerView(c);
                         result.Add(cv);
                     }
                 }
                 else
                 {
                     "".Substring(2);
                 }
                 return result;
             }
             catch (Exception e)
             {
                 Logger.Instance.Log("Program.Main.Test.GetAllCustomer", e.ToString());
                 return null;
             }

         }

    }
}   
